package zhaowenpu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import zhaowenpu.bean.ClassInfo;
import zhaowenpu.bean.Students;
import zhaowenpu.bean.User;
import zhaowenpu.tools.Conn;

public class Userlnfolmpl implements Userlnfo{
	public List<User> checkUser(String userName,String passWord) {
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<User> list=new ArrayList<User>();
		String sql=


				"select\n" +
						"t.user_id,\n" + 
						"t.user_name,\n" + 
						"t.user_password\n" + 
						" from USER_TEST_TABLE t where t.user_name=? and t.user_password=?";

		
			try {
				ps=con.prepareStatement(sql);
				ps.setString(1, userName);
				ps.setString(2, passWord);
				rs=ps.executeQuery();
				while (rs.next()) {
					User u=new User();
					u.setUsername(rs.getString(2));
					u.setUserpassword(rs.getString(3));
					list.add(u);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				Conn.cl(con, ps, rs);
			}

		
		return list;
	}
	@Override
	public List<User> checkUserName(String userName) {
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<User> list=new ArrayList<User>();
		String sql=

				"select\n" +
						"s.user_id,\n" + 
						"s.user_name,\n" + 
						"s.user_password\n" + 
						"from user_test_table s where s.user_name=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, userName);
			rs=ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				
				u.setUsername(rs.getString(2));
				u.setUserpassword(rs.getString(3));
				list.add(u);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps);
		}
		return list;
	}
	@Override
	public boolean adduser(User u) {
		String userName=u.getUsername();
		String pass=u.getUserpassword();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		
		boolean  execute=false;
		String sql="insert into user_test_table (user_name,user_password) values (?,?)";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2, pass);
			
			execute=ps.execute();
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps);
		}
		return execute;
		
	}
	@Override
	public String autoCode() {
		String code="";
		Connection con = Conn.getCon();
		PreparedStatement ps=null;
		 Date d=new Date();
		SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
		String format = sd.format(d);
		String likeCode="Dd-"+format+"-%";
		ResultSet rs=null;
		String sql= "select Max(CODE_T) from TEST_ORDER_CODE t where CODE_T like ?";
				
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, likeCode);
			rs=ps.executeQuery();
			while (rs.next()) {
			code = rs.getString(1);
				
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			Conn.cl(con,ps,rs);
		}
		return code;
		
	}
	@Override
	public List<Students> checrStudent(int sta, int end) {
		List<Students> list = new ArrayList<Students>();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=
			"select e2. * from (select rownum rn,e. * from(select*from stubents_info)e where rownum<=?)e2 where rn>=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, end);
			ps.setInt(2, sta);
			rs=ps.executeQuery();
			while (rs.next()) {
				Students st = new Students();
				st.setSId(rs.getString(2));
				st.setSName(rs.getString(3));
				st.setSSex(rs.getString(4));
				st.setSAddress(rs.getString(7));
				list.add(st);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		
		return list;
	}
	
	@Override
	public int checkCount() {
		
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) from Stubents_info";
		int pageCount=0;
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {
				pageCount=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		return pageCount;
	}
	@Override
	public List<Students> checkByNameStu(String name) {
		List<Students> list = new ArrayList<Students>();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=
                  "select *from stubents_info where s_name like '%"+name+"%'";
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {
				Students st = new Students();
				st.setSId(rs.getString(1));
				st.setSName(rs.getString(2));
				String stuName=rs.getString(2);
				st.setSName(stuName.replace(name, "<font color='red'>"+name+"</font>"));
				
				st.setSSex(rs.getString(3));
				st.setSAddress(rs.getString(6));
				list.add(st);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		
		return list;
	}
	@Override
	public void deleteStudents(String id) {
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="delete from stubents_info where s_id=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, id);
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		
	}
	@Override
	public List<Students> checkStudentsById(String id) {
		List<Students> list = new ArrayList<Students>();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=
			"select *from stubents_info where S_ID=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, id);
			rs=ps.executeQuery();
			while (rs.next()) {
				Students st = new Students();
				st.setSId(rs.getString(1));
				st.setSName(rs.getString(2));
				st.setSSex(rs.getString(3));
				st.setSAddress(rs.getString(6));
				list.add(st);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		
		return list;
	}

	@Override
	public void UpdateStudents(Students st) {
		String id=st.getSId();
		String name=st.getSName();
		String sex=st.getSSex();
		String address=st.getSAddress();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="update stubents_info set s_name=?,s_sex=?,s_address=? where s_id=?";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, sex);
			ps.setString(3, address);
			ps.setString(4, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
		
		
	}
	@Override
	public void InsertStudents(Students st) {
		String name=st.getSName();
		String sex=st.getSSex();
		String address=st.getSAddress();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="insert into stubents_info (s_name,s_sex,s_address)values(?,?,?)";
		try {
			ps=con.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, sex);
			ps.setString(3, address);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
		}
	}
	@Override
	public List<ClassInfo> checkClassInfo() {
				List<ClassInfo> list = new ArrayList<ClassInfo>();
				Connection con=Conn.getCon();
				PreparedStatement ps=null;
				ResultSet rs=null;
				String sql=
					"select * from class_info";
				try {
					ps=con.prepareStatement(sql);
					rs=ps.executeQuery();
					while (rs.next()) {
						ClassInfo ci = new ClassInfo();
						ci.setClassId(rs.getString(1));
						ci.setClassName(rs.getString(2));
						list.add(ci);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					Conn.cl(con, ps, rs);
		}
				return list;
	}
	@Override
	public List<String> checkAddressCheck() {
		List<String> list = new ArrayList<String>();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=
			"select s_address from stubents_info group by s_address";
		try {
			ps=con.prepareStatement(sql);
			rs=ps.executeQuery();
			while (rs.next()) {
			String Address=rs.getNString(1);
				
				list.add(Address);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
}
		return list;
	}
	@Override
	public List<Students> mutild(String classId, String sex, String address) {
		List<Students> list = new ArrayList<Students>();
		Connection con=Conn.getCon();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		StringBuffer sb=new StringBuffer("select * from stubents_info where 1=1 ");
		if(!sex.equals("")) {
			sb.append("  and s_sex='"+sex+"'");
		}
		if(!classId.equals("")) {
			sb.append("  and s_class_id='"+classId+"'");
		}
		if(!address.equals("")) {
			sb.append("  and s_address='"+address+"'");
		}
		System.out.println(sb);
		try {
			ps=con.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			while (rs.next()) {
				Students st=new Students();
				st.setSId(rs.getNString(1));
				st.setSName(rs.getNString(2));
				st.setSSex(rs.getNString(3));
				st.setSAddress(rs.getNString(6));
				list.add(st);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			Conn.cl(con, ps, rs);
}
		return list;
	}
}	
	


